package itu.dk.bosk.ex9;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * DataAccessObject for the person DB project (BOSK ex 9.2)
 * @author Michael Valentin
 */
public class DbDataAccessObject {

        static Connection con;
        static Statement stmt;
        static PreparedStatement insert;
        static PreparedStatement load;

        static {
            openConnection();
        }

        public static void openConnection() {
            try{
                Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
                String url = "jdbc:derby:PersonDB;create=true";
                con = DriverManager.getConnection(url);
                stmt = con.createStatement();
                insert = con.prepareStatement("INSERT INTO PERSON (PERSON_PK,FIRST_NAME,LAST_NAME) VALUES (?,?,?)");
                load = con.prepareStatement("SELECT * FROM PERSON WHERE PERSON_PK=?");
                CreateDatabase.dropTable(stmt);
                CreateDatabase.createTable(stmt);
            }catch(Exception ex){
                System.out.println("Something went wrong in the initialization..");
                ex.printStackTrace();
            }
        }

        public static void closeConnection() throws SQLException{
            stmt.close();
            con.close();
        }

        public static void savePerson(Person person) throws SQLException {
            String sql = "INSERT INTO PERSON VALUES(";
            sql += person.getId() + ",";
            sql += "'" + person.getFirstName() + "',";
            sql += "'" + person.getLastName() + "'";
            sql += ")";
            stmt.executeUpdate(sql);
        }

	public static void savePersonPrepared(Person person) throws SQLException {
            insert.setInt(1, person.getId());
            insert.setString(2, person.getFirstName());
            insert.setString(3, person.getLastName());
            insert.executeUpdate();
	}

	public static Person loadPerson(int id) throws SQLException {
            ResultSet res = stmt.executeQuery("SELECT * FROM PERSON WHERE PERSON_PK="+id);
            if(res.next()){
                return new Person(id, res.getString("FIRST_NAME"), res.getString("LAST_NAME"));
            }else{
                throw new IllegalArgumentException("The supplied id wasn't found in the database!");
            }
	}

        public static Person loadPersonPrepared(int id) throws SQLException {
            load.setInt(1, id);
            ResultSet res = load.executeQuery();
            if(res.next()){
                return new Person(id, res.getString("FIRST_NAME"), res.getString("LAST_NAME"));
            }else{
                throw new IllegalArgumentException("The supplied id wasn't found in the database!");
            }
        }

        public static void dropTable(){
            CreateDatabase.dropTable(stmt);
        }

        public static void createTable(){
            CreateDatabase.createTable(stmt);
        }

        public static void excuteQuery(String sql, QueryCallBack callBack) throws SQLException{
            ResultSet res = stmt.executeQuery(sql);
            try{
                while(res.next()){
                    callBack.processRecord(res);
                }
            }finally{
                res.close();
                //closeConnection();
            }
        }
}
